using OfficeOpenXml;
using System;
using System.IO;
using System.Linq;
using Urs.Core;
using Urs.Data.Domain.Stores;
using Urs.Services.Stores;
using Urs.Services.ExportImport.Help;
using Urs.Services.Media;

namespace Urs.Services.ExportImport
{
    public partial class ImportManager : IImportManager
    {
        private readonly IGoodsService _goodsService;
        private readonly ICategoryService _categoryService;
        private readonly IBrandService _brandService;
        private readonly IPictureService _pictureService;

        public ImportManager(IGoodsService goodsService, ICategoryService categoryService,
            IBrandService brandService, IPictureService pictureService)
        {
            this._goodsService = goodsService;
            this._categoryService = categoryService;
            this._brandService = brandService;
            this._pictureService = pictureService;
        }

        protected virtual int GetColumnIndex(string[] properties, string columnName)
        {
            if (properties == null)
                throw new ArgumentNullException("properties");

            if (columnName == null)
                throw new ArgumentNullException("columnName");

            for (int i = 0; i < properties.Length; i++)
                if (properties[i].Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                    return i + 1; //excel indexes start from 1
            return 0;
        }

        public virtual void ImportGoodssFromXlsx(Stream stream)
        {
            using (var xlPackage = new ExcelPackage(stream))
            {
                var worksheet = xlPackage.Workbook.Worksheets.FirstOrDefault();
                if (worksheet == null)
                    throw new UrsException("No worksheet found");

                var properties = new string[]
                {
                    "Name",
                    "ShortDescription",
                    "FullDescription",
                    "GoodsTemplateId",
                    "ShowOnHomePage",
                    "MetaKeywords",
                    "MetaDescription",
                    "MetaTitle",
                    "SeName",
                    "Published",
                    "SKU",
                    "ManufacturerPartNumber",
                    "IsShipEnabled",
                    "IsFreeShipping",
                    "AdditionalShippingCharge",
                    "ManageInventoryMethodId",
                    "StockQuantity",
                    "MinStockQuantity",
                    "NotifyAdminForQuantityBelow",
                    "BackorderModeId",
                    "AllowBackInStockSubscriptions",
                    "DisableBuyButton",
                    "DisableWishlistButton",
                    "Price",
                    "OldPrice",
                    "GoodsCost",
                    "SpecialPrice",
                    "SpecialPriceStartDateTimeUtc",
                    "SpecialPriceEndDateTimeUtc",
                    "Weight",
                    "Length",
                    "Width",
                    "Height",
                    "CreateTime",
                    "CategoryIds",
                    "BrandIds",
                    "Picture1",
                    "Picture2",
                    "Picture3",
                };


                int iRow = 2;
                while (true)
                {
                    bool allColumnsAreEmpty = true;
                    for (var i = 1; i <= properties.Length; i++)
                        if (worksheet.Cells[iRow, i].Value != null && !String.IsNullOrEmpty(worksheet.Cells[iRow, i].Value.ToString()))
                        {
                            allColumnsAreEmpty = false;
                            break;
                        }
                    if (allColumnsAreEmpty)
                        break;

                    string name = worksheet.Cells[iRow, GetColumnIndex(properties, "Name")].Value as string;
                    string shortDescription = worksheet.Cells[iRow, GetColumnIndex(properties, "ShortDescription")].Value as string;
                    string fullDescription = worksheet.Cells[iRow, GetColumnIndex(properties, "FullDescription")].Value as string;
                    int goodsTemplateId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "GoodsTemplateId")].Value);
                    bool showOnHomePage = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "ShowOnHomePage")].Value);
                    string metaKeywords = worksheet.Cells[iRow, GetColumnIndex(properties, "MetaKeywords")].Value as string;
                    string metaDescription = worksheet.Cells[iRow, GetColumnIndex(properties, "MetaDescription")].Value as string;
                    string metaTitle = worksheet.Cells[iRow, GetColumnIndex(properties, "MetaTitle")].Value as string;
                    string seName = worksheet.Cells[iRow, GetColumnIndex(properties, "SeName")].Value as string;
                    bool published = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "Published")].Value);
                    string sku = worksheet.Cells[iRow, GetColumnIndex(properties, "SKU")].Value as string;
                    string manufacturerPartNumber = worksheet.Cells[iRow, GetColumnIndex(properties, "ManufacturerPartNumber")].Value as string;
                    bool isShipEnabled = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsShipEnabled")].Value);
                    bool isFreeShipping = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsFreeShipping")].Value);
                    decimal additionalShippingCharge = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "AdditionalShippingCharge")].Value);
                    bool manageStockEnabled = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "ManageStockEnabled")].Value);
                    int stockQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "StockQuantity")].Value);
                    int minStockQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "MinStockQuantity")].Value);
                    int backorderModeId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "BackorderModeId")].Value);
                    bool allowBackInStockSubscriptions = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "AllowBackInStockSubscriptions")].Value);
                    int orderMinimumQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "OrderMinimumQuantity")].Value);
                    int orderMaximumQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "OrderMaximumQuantity")].Value);
                    bool disableBuyButton = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "DisableBuyButton")].Value);
                    bool disableWishlistButton = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "DisableWishlistButton")].Value);
                    decimal price = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Price")].Value);
                    decimal oldPrice = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "OldPrice")].Value);
                    decimal goodsCost = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "ProductCost")].Value);
                    decimal? specialPrice = null;
                    var specialPriceExcel = worksheet.Cells[iRow, GetColumnIndex(properties, "SpecialPrice")].Value;
                    if (specialPriceExcel != null)
                        specialPrice = Convert.ToDecimal(specialPriceExcel);
                    DateTime? specialPriceStartDateTimeUtc = null;
                    var specialPriceStartDateTimeUtcExcel = worksheet.Cells[iRow, GetColumnIndex(properties, "SpecialPriceStartDateTimeUtc")].Value;
                    if (specialPriceStartDateTimeUtcExcel != null)
                        specialPriceStartDateTimeUtc = DateTime.FromOADate(Convert.ToDouble(specialPriceStartDateTimeUtcExcel));
                    DateTime? specialPriceEndDateTimeUtc = null;
                    var specialPriceEndDateTimeUtcExcel = worksheet.Cells[iRow, GetColumnIndex(properties, "SpecialPriceEndDateTimeUtc")].Value;
                    if (specialPriceEndDateTimeUtcExcel != null)
                        specialPriceEndDateTimeUtc = DateTime.FromOADate(Convert.ToDouble(specialPriceEndDateTimeUtcExcel));

                    decimal weight = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Weight")].Value);
                    decimal length = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Length")].Value);
                    decimal width = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Width")].Value);
                    decimal height = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Height")].Value);
                    DateTime createdOnUtc = DateTime.FromOADate(Convert.ToDouble(worksheet.Cells[iRow, GetColumnIndex(properties, "CreateTime")].Value));
                    int categoryId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "CategoryId")].Value);
                    int brandId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "BrandIds")].Value);
                    string picture1 = worksheet.Cells[iRow, GetColumnIndex(properties, "Picture1")].Value as string;
                    string picture2 = worksheet.Cells[iRow, GetColumnIndex(properties, "Picture2")].Value as string;
                    string picture3 = worksheet.Cells[iRow, GetColumnIndex(properties, "Picture3")].Value as string;

                    var goods = _goodsService.GetGoodsBySku(sku);
                    if (goods != null)
                    {
                        goods.Name = name;
                        goods.ShortDescription = shortDescription;
                        goods.FullDescription = fullDescription;
                        goods.ShowOnHomePage = showOnHomePage;
                        goods.Published = published;
                        goods.CreateTime = createdOnUtc;
                        goods.UpdateTime = DateTime.Now;
                        goods.CategoryId = categoryId;
                        goods.BrandId = brandId;
                        goods.Sku = sku;
                        goods.ManufacturerPartNumber = manufacturerPartNumber;
                        goods.IsShipEnabled = isShipEnabled;
                        goods.IsFreeShipping = isFreeShipping;
                        goods.AdditionalShippingCharge = additionalShippingCharge;
                        goods.ManageStockEnabled = manageStockEnabled;
                        goods.StockQuantity = stockQuantity;
                        goods.MinStockQuantity = minStockQuantity;
                        goods.Price = price;
                        goods.OldPrice = oldPrice;
                        goods.SpecialPrice = specialPrice;
                        goods.SpecialStartTime = specialPriceStartDateTimeUtc;
                        goods.SpecialEndTime = specialPriceEndDateTimeUtc;
                        goods.Weight = weight;
                        goods.Length = length;
                        goods.Width = width;
                        goods.Height = height;

                        _goodsService.UpdateGoods(goods);
                    }
                    else
                    {
                        goods = new Goods()
                        {
                            Name = name,
                            ShortDescription = shortDescription,
                            FullDescription = fullDescription,
                            ShowOnHomePage = showOnHomePage,
                            Published = published,
                            CreateTime = createdOnUtc,
                            UpdateTime = DateTime.Now,
                            CategoryId = categoryId,
                            BrandId = brandId,
                            Sku = sku,
                            ManufacturerPartNumber = manufacturerPartNumber,
                            IsShipEnabled = isShipEnabled,
                            IsFreeShipping = isFreeShipping,
                            AdditionalShippingCharge = additionalShippingCharge,
                            ManageStockEnabled = manageStockEnabled,
                            StockQuantity = stockQuantity,
                            MinStockQuantity = minStockQuantity,
                            Price = price,
                            OldPrice = oldPrice,
                            SpecialPrice = specialPrice,
                            SpecialStartTime = specialPriceStartDateTimeUtc,
                            SpecialEndTime = specialPriceEndDateTimeUtc,
                            Weight = weight,
                            Length = length,
                            Width = width,
                            Height = height
                        };
                        _goodsService.InsertGoods(goods);

                    }

                    foreach (var picture in new string[] { picture1, picture2, picture3 })
                    {
                        if (String.IsNullOrEmpty(picture))
                            continue;

                        var p = _pictureService.InsertPicture(File.ReadAllBytes(picture), "image/jpeg", string.Empty, true);

                        var pp = new GoodsPicture()
                        {
                            PictureId = p.Id,
                            DisplayOrder = 1,
                        };
                        _goodsService.InsertGoodsPicture(pp);
                    }
                    iRow++;
                }
            }
        }


        public virtual void ImportBrandsFromXlsx(Stream stream)
        {
            var properties = new[]
            {
                new PropertyByName<Brand>("Id"),
                new PropertyByName<Brand>("Name"),
                new PropertyByName<Brand>("Description"),
                new PropertyByName<Brand>("MetaKeywords"),
                new PropertyByName<Brand>("MetaDescription"),
                new PropertyByName<Brand>("MetaTitle"),
                new PropertyByName<Brand>("SeName"),
                new PropertyByName<Brand>("Picture"),
                new PropertyByName<Brand>("PageSize"),
                new PropertyByName<Brand>("PriceRanges"),
                new PropertyByName<Brand>("Published"),
                new PropertyByName<Brand>("DisplayOrder")
            };

            var manager = new PropertyManager<Brand>(properties);

            using (var xlPackage = new ExcelPackage(stream))
            {
                var worksheet = xlPackage.Workbook.Worksheets.FirstOrDefault();
                if (worksheet == null)
                    throw new UrsException("No worksheet found");

                var iRow = 2;

                while (true)
                {
                    var allColumnsAreEmpty = manager.GetProperties
                        .Select(property => worksheet.Cells[iRow, property.PropertyOrderPosition])
                        .All(cell => cell == null || cell.Value == null || String.IsNullOrEmpty(cell.Value.ToString()));

                    if (allColumnsAreEmpty)
                        break;

                    manager.ReadFromXlsx(worksheet, iRow);

                    var brand = _brandService.GetById(manager.GetProperty("Id").IntValue);

                    var isNew = brand == null;

                    brand = brand ?? new Brand();

                    if (isNew)
                        brand.CreateTime = DateTime.Now;

                    brand.Name = manager.GetProperty("Name").StringValue;
                    brand.Description = manager.GetProperty("Description").StringValue;
                    brand.MetaKeywords = manager.GetProperty("MetaKeywords").StringValue;
                    brand.MetaDescription = manager.GetProperty("MetaDescription").StringValue;
                    brand.MetaTitle = manager.GetProperty("MetaTitle").StringValue;
                    brand.PageSize = manager.GetProperty("PageSize").IntValue;
                    brand.PriceRanges = manager.GetProperty("PriceRanges").StringValue;
                    brand.Published = manager.GetProperty("Published").BooleanValue;
                    brand.DisplayOrder = manager.GetProperty("DisplayOrder").IntValue;
                    brand.PictureId = brand.PictureId;

                    brand.UpdateTime = DateTime.Now;

                    if (isNew)
                        _brandService.Insert(brand);
                    else
                        _brandService.Update(brand);

                    iRow++;
                }
            }
        }

    }
}
